﻿Imports System.Data.Linq
Imports System.Linq

Public Class HBLRepository
    Private dc As New TanNamChinhDataContext

    Public Sub New()
        dc = GeneralClassDAL.dc
    End Sub

    'Public Function GetHBLList(ByVal id_file As Integer) As IQueryable(Of tblHBL)
    '    Dim query As IQueryable(Of tblHBL) = From p In dc.tblHBLs
    '                              Where p.tblFileMBL.ID_File = id_file
    '                              Select p
    '    Return query
    'End Function

    ''' <summary>
    ''' Lấy danh sách HBL của File và các thông tin khai báo thuế nếu có
    ''' </summary>
    ''' <param name="id_FileMBL"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetHBLByFileMBL(ByVal id_FileMBL As Integer) As IQueryable
        Dim query As IQueryable = From p In dc.tblHBLs
                                  Where p.ID_FileMBL = id_FileMBL
                                  Select p.ID_HBL, p.HBLNo, p.GW, p.NW, p.CBM, p.NotifyParty, p.GoodDescription,
                                  p.tblTaxReturn.TaxReturnNo, p.tblTaxReturn.CreatedDate, p.tblTaxReturn.ExpiredDate, p.tblTaxReturn.Amount, p.tblTaxReturn.VATPercent
        Return query
    End Function

    ''' <summary>
    ''' Lấy danh sách HBL của File và các thông tin khai báo thuế nếu có
    ''' </summary>
    ''' <param name="id_file"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetHBLByFile(ByVal id_file As Integer) As IQueryable
        Dim query As IQueryable = From p In dc.tblHBLs
                                  Where p.tblFileMBL.ID_File = id_file
                                  Select p.ID_HBL, p.HBLNo, p.GW, p.NW, p.CBM, p.NotifyParty, p.GoodDescription,
                                  p.tblTaxReturn.TaxReturnNo, p.tblTaxReturn.CreatedDate, p.tblTaxReturn.ExpiredDate, p.tblTaxReturn.Amount, p.tblTaxReturn.VATPercent
        Return query
    End Function

    Public Function SelectRecordById(ByVal id As Integer) As tblHBL
        Dim HBL = dc.tblHBLs.SingleOrDefault(Function(p As tblHBL) p.ID_HBL = id)
        Return HBL
    End Function

    Public Sub Insert(ByVal obj As tblHBL)
        dc.tblHBLs.InsertOnSubmit(obj)
        dc.SubmitChanges()
    End Sub

    Public Sub Update(ByVal obj As tblHBL)
        dc.Refresh(RefreshMode.KeepChanges, obj)
        dc.SubmitChanges(ConflictMode.ContinueOnConflict)
    End Sub

    ''' <summary>
    ''' Xóa ReceiptVoucher không dùng. ReceiptVoucher không dùng khi:
    '''    + Không tồn tại SellingFee nào và không có nằm trong AdvanceVoucher ( ==> xem lại thiết kế CSDL hiện tại hơi ngược giữa tblAdvanceVoucher và tblReceiptVoucher)
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub Delete_UnusedReceiptVoucherAndCascade()
        'Xóa ReceiptVoucher không tồn tại SellingFee và không nằm trong tblAdvanceVoucher
        Dim query = From p In dc.tblReceiptVouchers
                    Where p.tblSellingFees.Count = 0 And p.tblAdvanceVouchers.Count = 0
                    Select p
        For Each obj As tblReceiptVoucher In query
            dc.tblReceiptVouchers.DeleteOnSubmit(obj)
        Next
        dc.SubmitChanges()

    End Sub

    ''' <summary>
    ''' Xóa HBL sẽ thực hiện:
    ''' + Set null cont
    ''' + Cascade delte tblComercialInvoice
    ''' + Cascade delete tblHBLExchange
    ''' + Cascade delete tblSellingFee:
    '''    - Xóa các phiếu thu không có phí nằm ở tblSellingFee
    ''' </summary>
    ''' <param name="obj"></param>
    ''' <remarks></remarks>
    Public Sub Delete(ByVal obj As tblHBL)
        'Set ID_HBL = nothing của Cont 
        Dim query = From p In dc.tblConts
                    Where p.tblHBL.ID_HBL = obj.ID_HBL
                    Select p
        For Each objCont As tblCont In query
            objCont.ID_HBL = Nothing
        Next

        '=================================
        'Xóa HBL
        dc.tblHBLs.DeleteOnSubmit(obj)
        dc.SubmitChanges()
        '=================================
        'Xóa phiếu thu không có phí SellingFee (do xóa HBL cascade delete SellingFee)
        Delete_UnusedReceiptVoucherAndCascade()
    End Sub

    Public Function CalculateSumNW(ByVal ID_HBL As Integer) As Double
        Dim sum As Double
        Try
            sum = (From p In dc.tblConts
                    Where (p.ID_HBL = ID_HBL)
                     Select p).Sum(Function(p) p.NW).Value
        Catch ex As Exception
            sum = 0
        End Try

        Return sum
    End Function

    Public Function CalculateSumGW(ByVal ID_HBL As Integer) As Double
        Dim sum As Double
        Try
            sum = (From p In dc.tblConts
                    Where (p.ID_HBL = ID_HBL)
                     Select p).Sum(Function(p) p.GW).Value
        Catch ex As Exception
            sum = 0
        End Try

        Return sum
    End Function

    Public Function CalculateSumCBM(ByVal ID_HBL As Integer) As Double
        Dim sum As Double
        Try
            sum = (From p In dc.tblConts
                    Where (p.ID_HBL = ID_HBL)
                     Select p).Sum(Function(p) p.CBM).Value
        Catch ex As Exception
            sum = 0
        End Try

        Return sum
    End Function
End Class
